﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Text;
using Model;
using DB;

/// <summary>
/// ArchiveInformationService 的摘要说明
/// </summary>
/// 
namespace ArchivesManagement
{

    public class ArchiveInformationService
    {
        string sql = string.Empty;
        public ArchiveInformationService()
        {

        }
        //通过档案编号获取档案信息实体对象
        public ArchiveInformation GetArchiveInformation(string  fileNumber) 
        {
           ArchiveInformation archiveInformations = new ArchiveInformation();
            sql = "select * from ArchiveInformation where FileNumber="+"'"+fileNumber+"'";
            
            using (SqlDataReader dr = DB.DBHelper.Exc(sql))
            {
                if(dr.Read())
                {
                    ArchiveInformation archiveInformation = new ArchiveInformation();
                    archiveInformation.StudentID = Convert.ToString(dr["StudentId"]);
                    archiveInformation.Address = Convert.ToString(dr["Address"]);
                    archiveInformation.TransferTime = Convert.ToDateTime(dr["TransferTime"]);
                    archiveInformation.AdministratorID = Convert.ToString(dr["AdministratorID"]);
                    
                }
            }
            return archiveInformations;
        }

        //通过学号获取档案编号
        public string GetFileNumberByStudnetID(string studentID)
        {
            string fileNumber = string.Empty;
            sql= "select FileNumber from ArchiveInformation where StudentID="+"'"+studentID+"'";
            SqlDataReader dr = DB.DBHelper.Exc(sql);
            if (dr.Read())
            {
                fileNumber = Convert.ToString(dr["FileNumber"]);
            }
            return fileNumber;
        }

        //通过档案编号获取学号
        public string   GetStudnetIDByFileNumber(string   fileumber)
        {
            string studentID = string .Empty;
            sql = "select StudentID from ArchiveInformation where FileNumber=" + "'" + fileumber + "'";
            SqlDataReader dr = DB.DBHelper.Exc(sql);
            if (dr.Read())
            {
                studentID= Convert.ToString(dr["StudentID"]);
            }

            return studentID;
        }

        
        //通过档案编号获取档案信息
        public IList<ArchiveInformation> GetArchiveInformationByFileNumber(string fileNumber)
        {
            IList<ArchiveInformation> archiveInformations = new List<ArchiveInformation>();
            sql = "select * from ArchiveInformationwhere FileNumber = " + fileNumber.ToString();
            
            using (SqlDataReader dr = DB.DBHelper.Exc(sql))
            {
                while (dr.Read())
                {
                    ArchiveInformation archiveInformation = new ArchiveInformation();
                    //archiveInformation.FileNumber = Convert.ToString(dr["FileNumber"]);
                    archiveInformation.StudentID = Convert.ToString(dr["StudentId"]);
                    archiveInformation.Address = Convert.ToString(dr["Address"]);
                    archiveInformation.TransferTime = Convert.ToDateTime(dr["TransferTime"]);
                    archiveInformation.AdministratorID = Convert.ToString(dr["AdministratorID"]);
                    archiveInformations.Add(archiveInformation);
                }
            }
            return archiveInformations;
        }

        //获取档案信息
        public IList<ArchiveInformation> GetArchiveInformation()  
        {
            IList<ArchiveInformation> archiveInformations = new List<ArchiveInformation>();
            sql = "select * from ArchiveInformation";

            using (SqlDataReader dr = DB.DBHelper.Exc(sql))
            {
                while (dr.Read())
                {
                    ArchiveInformation archiveInformation = new ArchiveInformation();
                    archiveInformation.FileNumber = Convert.ToString(dr["FileNumber"]);
                    archiveInformation.StudentID = Convert.ToString(dr["StudentId"]);
                    archiveInformation.Address = Convert.ToString(dr["Address"]);
                    archiveInformation.TransferTime = Convert.ToDateTime(dr["TransferTime"]);
                    archiveInformation.AdministratorID = Convert.ToString(dr["AdministratorID"]);
                    archiveInformations.Add(archiveInformation);
                }
            }
            return archiveInformations;
        }

         //删除档案信息
        public void DeleteArchiveInformationByFileNumber(string  fileNumber)  
        {
            sql = "delete from ArchiveInformation where FileNumber=" + Convert.ToString(fileNumber);
            DB.DBHelper.ExecuteCommand(sql);
        }

         //修改档案信息
        public int  ModifyArchiveInformation(ArchiveInformation archiveInformation)  
        {
            int id = 0;
            StringBuilder updateArchiveInformation = new StringBuilder();
            updateArchiveInformation.Append("update ArchiveInformation set StudentId='" + archiveInformation.StudentID
                + "',Address='" + archiveInformation.Address + "',TransferTime='" + archiveInformation.TransferTime
                + "',AdministratorID='" + archiveInformation.AdministratorID + "'where FileNumber="+archiveInformation.FileNumber);
            sql = updateArchiveInformation.ToString();
            id=DB.DBHelper.ExecuteCommand(sql);
            return id;
        }

        //增加档案信息
        public int   AddArchiveInformation(ArchiveInformation archiveInformation)  
        {
            int id=0 ;
            StringBuilder addArchiveInformation = new StringBuilder();
            addArchiveInformation.Append("insert into ArchiveInformation(FileNumber,StudentId,Address,AdministratorID,TransferTime) values('" +archiveInformation.FileNumber+"','" 
                + archiveInformation.StudentID + "','"+archiveInformation.Address+"','" + archiveInformation.AdministratorID + "','" + archiveInformation.TransferTime+ "');");
            sql = addArchiveInformation.ToString();
            id = DB.DBHelper.ExecuteCommand(sql);
            return id;
        }


    }
}